package DaosImpl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.FileSystemXmlApplicationContext;

import Beans.OrderForm;
import Beans.CancelOrder;
import Beans.DataBase;
import Daos.OrderDao;

public class OrderDaoImpl implements OrderDao{

	@Override
	public int countRow(String sql) throws Exception {
		Connection conn = null;
        Statement stat = null;
        int rowCount = 0;
        ApplicationContext context = new FileSystemXmlApplicationContext("D:\\MyEcilipseWorkSpace\\HotelSystem\\src\\applicationContext.xml");
        DataBase dataBase = (DataBase)context.getBean("dataBase");
        String url = dataBase.getUrl()+dataBase.getDataBaseName()+dataBase.getAccount()+dataBase.getPwd();
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	//String url =  "jdbc:sqlserver://localhost:1433;" +
        			  //"databaseName=management_system;user=sa;password=Zls062813;";
			conn = DriverManager.getConnection(url);
			stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			rs.next();
			rowCount = rs.getInt(1);
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if(conn!=null){
                    conn.close();
                }
                if(stat!=null){
                	stat.close();
                }
            }catch (Exception ex){
            	ex.printStackTrace();
            }
        }
        return rowCount;
	}

	@Override
	public ArrayList<OrderForm> queryViewOrder() throws Exception {
		Connection conn = null;
        Statement stat = null;
        ArrayList orderList = new ArrayList();
        ApplicationContext context = new FileSystemXmlApplicationContext("D:\\MyEcilipseWorkSpace\\HotelSystem\\src\\applicationContext.xml");
        DataBase dataBase = (DataBase)context.getBean("dataBase");
        String url = dataBase.getUrl()+dataBase.getDataBaseName()+dataBase.getAccount()+dataBase.getPwd();
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	//String url =  "jdbc:sqlserver://localhost:1433;" +
        			  //"databaseName=management_system;user=sa;password=Zls062813;";
			conn = DriverManager.getConnection(url);
			String sql = "SELECT * FROM V_Order_Form;";
			stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
            while(rs.next()){
            	OrderForm orderForm = new OrderForm();
            	orderForm.setOfID(Integer.parseInt(rs.getString("of_id")));
            	orderForm.setName(rs.getString("client_name"));
            	orderForm.setOfInDate(rs.getString("of_enterdate"));
            	orderForm.setIdCard(rs.getString("client_id"));
            	//System.out.println(rs.getString("client_id"));
            	orderForm.setOfWantOutDate(rs.getString("of_wantleavedate"));
            	if(rs.getString("of_leavedate")==null){
            		orderForm.setOfOutDate(" ");
            	}else{
            		orderForm.setOfOutDate(rs.getString("of_leavedate"));
            	}
            	orderForm.setOfPayMoney(Float.parseFloat(rs.getString("of_paymoney")));
            	orderForm.setRoomid(String.valueOf(rs.getInt("r_id")));
            	orderList.add(orderForm);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if(conn!=null){
                    conn.close();
                }
                if(stat!=null){
                	stat.close();
                }
            }catch (Exception ex){
            	ex.printStackTrace();
            }
        }
        return orderList;
	}

	@Override
	public ArrayList<CancelOrder> queryAllCancelOrder() throws Exception {
		Connection conn = null;
        ArrayList<CancelOrder> list = new ArrayList<CancelOrder>();
        ApplicationContext context = new FileSystemXmlApplicationContext("D:\\MyEcilipseWorkSpace\\HotelSystem\\src\\applicationContext.xml");
        DataBase dataBase = (DataBase)context.getBean("dataBase");
        String url = dataBase.getUrl()+dataBase.getDataBaseName()+dataBase.getAccount()+dataBase.getPwd();
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	//String url =  "jdbc:sqlserver://localhost:1433;" +
        			  //"databaseName=management_system;user=sa;password=Zls062813;";
			conn = DriverManager.getConnection(url);
			String sql = "select client_name,Client.client_id,Order_Form.of_id,Room.r_id,of_paymoney,rt_earnest,rt_rent_money,of_enterdate,of_wantleavedate"
					+" from Client,Order_Form,Open_Room,Room,Room_Type"+" where of_leavedate is null and Client.client_id = Order_Form.client_id and Order_Form.of_id = Open_Room.of_id"
					+" and Open_Room.r_id = Room.r_id and Room.rt_id = Room_Type.rt_id;";
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
            while(rs.next()){
            	CancelOrder cancel = new CancelOrder();
            	cancel.setName(rs.getString(1));
            	cancel.setIdCard(rs.getString(2));
            	cancel.setOrderId(rs.getInt(3));
            	cancel.setRoomId(rs.getInt(4));
            	cancel.setPayMoney(rs.getFloat(5));
            	cancel.setEarnest(rs.getFloat(6));
            	cancel.setRent(rs.getFloat(7));
            	cancel.setInDate(rs.getString(8));
            	cancel.setWantOutDate(rs.getString(9));
            	list.add(cancel);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if(conn!=null){
                    conn.close();
                }
            }catch (Exception ex){
            	ex.printStackTrace();
            }
        }
        return list;
	}

	@Override
	public ArrayList<OrderForm> queryMyOrder(String account) throws Exception {
		Connection conn = null;
        Statement stat = null;
        ArrayList orderList = new ArrayList();
        ApplicationContext context = new FileSystemXmlApplicationContext("D:\\MyEcilipseWorkSpace\\HotelSystem\\src\\applicationContext.xml");
        DataBase dataBase = (DataBase)context.getBean("dataBase");
        String url = dataBase.getUrl()+dataBase.getDataBaseName()+dataBase.getAccount()+dataBase.getPwd();
        try{
        	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        	//String url =  "jdbc:sqlserver://localhost:1433;" +
        			  //"databaseName=management_system;user=sa;password=Zls062813;";
			conn = DriverManager.getConnection(url);
			String sql = "SELECT * FROM V_Order_Form,Role_Info where account='"+account+"' AND V_Order_Form.client_id = Role_Info.client_id;";
			stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
            while(rs.next()){
            	OrderForm orderForm = new OrderForm();
            	orderForm.setOfID(Integer.parseInt(rs.getString("of_id")));
            	orderForm.setName(rs.getString("client_name"));
            	orderForm.setOfInDate(rs.getString("of_enterdate"));
            	orderForm.setOfWantOutDate(rs.getString("of_wantleavedate"));
            	System.out.println(rs.getString("of_wantleavedate"));
            	if(rs.getString("of_leavedate")==null){
            		orderForm.setOfOutDate(" ");
            	}else{
            		orderForm.setOfOutDate(rs.getString("of_leavedate"));
            	}
            	orderForm.setOfPayMoney(Float.parseFloat(rs.getString("of_paymoney")));
            	orderForm.setRoomid(String.valueOf(rs.getInt("r_id")));
            	orderList.add(orderForm);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }finally {
            try{
                if(conn!=null){
                    conn.close();
                }
                if(stat!=null){
                	stat.close();
                }
            }catch (Exception ex){
            	ex.printStackTrace();
            }
        }
        return orderList;
	}
	
}
